<?php
defined("BASEPATH") or exit('No direct script access allowed');
/**
 * Created by PhpStorm.
 * User: lddsb
 * Date: 2016/8/28/028
 * Time: 18:05
 */
class Daily extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();

    }

    /**
     * 日报表主页
     */
    public function index()
    {
        $this->load->view('daily_home');
    }

    /**
     * 将上传的Excel导入到MySQL中
     */
    public function upload()
    {
        //  引入PHPExcel
        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');
        //  打开数据库链接
        $this->load->database();
        //  获取汇率
        $this->db->select('currency, exrate');
        $rateQuery= $this->db->get('rate');
        foreach ($rateQuery->result_array() as $val) {
            $rate[$val['currency']] = $val['exrate'];
        }
        //  获取上传的文件
        $filename = $_FILES['file1']['tmp_name'];
        //  默认文件为xls格式
        $ExcelRead = new PHPExcel_Reader_Excel5();
        //  判断是否是xls格式
        if (!$ExcelRead->canRead($filename)) {
            //  如果不是xls格式，则尝试使用xlsx读取该文件
            $ExcelRead = new PHPExcel_Reader_Excel2007();
            //  判断是否是xlsx格式
            if (!$ExcelRead->canRead($filename)) {
                //  如果不是xlsx格式，则
                echo '<script>alert("请上传Excel文件！");window.location.href="/daily";</script>';
                exit;
            }
        }
        //  如果上传成功，则先清空表中原有的数据
        $this->db->truncate('insert_data');
        //  设置只读取文本，不读取格式
        $ExcelRead->setReadDataOnly(true);
        //  读取Excel文件
        $ExcelReader = $ExcelRead->load($filename);
        //  获取第一张sheet
        $ExcelSheet = $ExcelReader->getSheet(0);
        //  获取总行数
        $totalRows = $ExcelSheet->getHighestRow();

        for ($i = 3; $i < $totalRows; $i ++) {
            $gatewayNo = $ExcelSheet->getCell('C'.$i)->getValue();
            $currency = $ExcelSheet->getCell('F'.$i)->getValue();
            $amount = $ExcelSheet->getCell('G'.$i)->getValue();
            $channel = $ExcelSheet->getCell('H'.$i)->getValue();
            $status = $ExcelSheet->getCell('Y'.$i)->getValue();
            if ('成功' == $status)
                $status = 1;
            elseif ('失败' == $status)
                $status = 0;
            elseif ('待处理' == $status)
                $status = -1;
            else
                $status = -2;
            $merNo = substr($gatewayNo, 0, 5);
            $data[$i-3] = [
                'merNo' => $merNo,
                'gatewayNo' => $gatewayNo,
                'currency' => $currency,
                'amount' => $amount,
                'channel' => $channel,
                'status' => $status,
            ];
        }
        if ($this->db->insert_batch('insert_data', $data)) {
            echo '上传成功！';
        }
        exit;
    }

    public function top20()
    {

    }

    /**
     * 文件上传
     */
    public function test()
    {
        $exclude_mer = array(
            '10000', '10230', '10601', '10613', '10308', '10031', '10479', '10480', '10537', '10191', '10499'
            );
        $this->load->database();
        $query = $this->db->query('select currency, exrate from rate');
        $result = $query->result_array();
        foreach($result as $val) {
            $rate[$val['currency']] = $val['exrate'];
        }
        // 引入PHPExcel
        $this->load->library('PHPExcel');
        $this->load->library('PHPExcel/IOFactory');
        $objReader = IOFactory::createReader('Excel5');
        // 获取缓存的文件名
        $filename = $_FILES['file1']['tmp_name'];
        $objReader->setReadDataOnly(true);
        // 读取缓存文件
        $objPHPExcel = $objReader->load($filename);
        // 获取第一张sheet
        $sheet = $objPHPExcel->getSheet(0);
        // 获取Excel表格的行数
        $row = $sheet->getHighestRow();
        $wait = array('10423002', '10401001', '10951001');
        $wildCardAmount = 0;
        $wildCardItems = 0;
        for($i = 3; $i < $row; $i++) {
            $gatewayNo = $sheet->getCell('C'.$i)->getValue();
//            $channel = $sheet->getCell('H'.$i)->getValue();
            $merNo = substr($gatewayNo, 0, 5);
            if(!in_array($merNo, $exclude_mer)) {
                $currency = $sheet->getCell('F'.$i)->getValue();
                $amount = $sheet->getCell('G'.$i)->getValue();
                $status = $sheet->getCell('Y'.$i)->getValue();
                foreach($rate as $key => $val) {
                    if($key == $currency) {
                        $data[$i-2]['conversion_amount'] = $amount * $val;
                        $data[$i-2]['conversion_currency'] = 'USD';
                        break;
                    }
                }
                switch ($status) {
                    case '成功':
                        $status = '1';
                        break;
                    case '失败':
                        $status = '0';
                        break;
                    case '待处理':
                        if (in_array($merNo, $wait)) {
                            $status = '1';
                            $wildCardAmount += $amount;
                            $wildCardItems ++;
                        } else {
                            $status = '-1';
                        }
                        break;
                    default:
                        $status = '-2';
                        break;
                }
                $data[$i-2]['merNo'] = $merNo;
                $data[$i-2]['amount'] = $amount;
                $data[$i-2]['currency'] = $currency;
                $data[$i-2]['status'] = $status;
            }
            $_SESSION['wildCardAmount'] = $wildCardAmount;
            $_SESSION['wildCardItems'] = $wildCardItems;
        }
        var_dump($data);
        exit;
        //  插入数据库中
        $this->db->insert_batch('import_data', $data);
        // 删除缓存文件
        unlink($filename);
        exit;
    }

    public function rate()
    {

    }
}